package sk.vasicek.drinkmanager.service;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import sk.vasicek.drinkmanager.domain.DrinkEntry;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DrinkHistoryHelper extends SQLiteOpenHelper {

	private static final String TAG = DrinkHistoryHelper.class.getSimpleName();
	private static final int DATABASE_VERSION = 3;
	private static final String DATABASE_NAME = "drink_history_db";
	public static final String ID = "drink_id";
	public static final String DRINK_NAME = "drink_name";
	private static final String NOTE = "drink_note";
	public static final String ALCOHOL_CONCENTRATION = "concentration";
	public static final String VOLUME = "volume";
	public static final String TIME_DRINKED = "time_drinked";
	public static final String PRICE = "price";
	private static final String DRINK_HISTORY_TABLE_NAME = "drink_history";
	private static final String DRINK_ENTRIES_TABLE_CREATE = "CREATE TABLE " + DRINK_HISTORY_TABLE_NAME + " (" + ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + DRINK_NAME + " TEXT, " + NOTE + " TEXT, "
			+ PRICE + " DOUBLE, " + ALCOHOL_CONCENTRATION + " DOUBLE, " + VOLUME + " DOUBLE, " + TIME_DRINKED + " TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);";
	private static final String DRINK_ENTRIES_TABLE_DROP = "DROP TABLE " + DRINK_HISTORY_TABLE_NAME + ";";

	public DrinkHistoryHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DRINK_ENTRIES_TABLE_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Version 0.7.8.0 -> 0.7.8.5
		if (oldVersion == 3 && newVersion == 4) {
			ArrayList<DrinkEntry> drinkPoints = new ArrayList<DrinkEntry>();
			Cursor cursor = db.query(DRINK_HISTORY_TABLE_NAME, new String[] { TIME_DRINKED, DRINK_NAME, ALCOHOL_CONCENTRATION, VOLUME }, null, null, null, null, TIME_DRINKED + " DESC");
			while (cursor.getCount() > 0 && cursor.moveToNext()) {
				String place = cursor.getString(cursor.getColumnIndex(DRINK_NAME));
				Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME_DRINKED)));
				double latitude = cursor.getDouble(cursor.getColumnIndex(ALCOHOL_CONCENTRATION));
				double longitude = cursor.getDouble(cursor.getColumnIndex(VOLUME));
				DrinkEntry drinkPoint = new DrinkEntry(latitude, longitude, place, "Location: " + String.valueOf(latitude).subSequence(0, 6) + " " + String.valueOf(longitude).subSequence(0, 6) + " "
						+ timeOfLocation.getDate() + "." + timeOfLocation.getMonth() + ". " + timeOfLocation.getHours() + ":" + timeOfLocation.getMinutes() + ":" + timeOfLocation.getSeconds());
				drinkPoints.add(drinkPoint);
			}

			db.execSQL(DRINK_ENTRIES_TABLE_DROP);
			db.execSQL(DRINK_ENTRIES_TABLE_CREATE);
			for (DrinkEntry drinkPoint : drinkPoints) {
				ContentValues contentValues = new ContentValues();
				String nameEscaped = StringHelper.avoidInjectionString(drinkPoint.getName());
				contentValues.put(DRINK_NAME, nameEscaped);
				String noteEscaped = StringHelper.avoidInjectionString(drinkPoint.getNote());
				contentValues.put(NOTE, noteEscaped);
				contentValues.put(PRICE, 0);
				contentValues.put(ALCOHOL_CONCENTRATION, drinkPoint.getConcentration());
				contentValues.put(VOLUME, drinkPoint.getVolume());

				db.insert(DRINK_HISTORY_TABLE_NAME, null, contentValues);
			}
		} else {
			db.execSQL(DRINK_ENTRIES_TABLE_DROP);
			db.execSQL(DRINK_ENTRIES_TABLE_CREATE);
		}
	}

	public ArrayList<DrinkEntry> getAllDrinksFromHistory() {
		SQLiteDatabase db = getReadableDatabase();
		ArrayList<DrinkEntry> drinkPoints = new ArrayList<DrinkEntry>();
		Cursor cursor = db.query(DRINK_HISTORY_TABLE_NAME, new String[] { ID, TIME_DRINKED, DRINK_NAME, NOTE, PRICE, ALCOHOL_CONCENTRATION, VOLUME }, null, null, null, null, TIME_DRINKED + " DESC");
		while (cursor.getCount() > 0 && cursor.moveToNext()) {

			Integer id = cursor.getInt(cursor.getColumnIndex(ID));
			String name = cursor.getString(cursor.getColumnIndex(DRINK_NAME));
			String note = cursor.getString(cursor.getColumnIndex(NOTE));
			double price = cursor.getDouble(cursor.getColumnIndex(PRICE));
			Timestamp timeDrinked = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME_DRINKED)));
			Date time = new Date(timeDrinked.getTime());
			double concentration = cursor.getDouble(cursor.getColumnIndex(ALCOHOL_CONCENTRATION));
			double volume = cursor.getDouble(cursor.getColumnIndex(VOLUME));
			DrinkEntry drinkEntry = new DrinkEntry(id, concentration, volume, name, note, price, time);
			drinkPoints.add(drinkEntry);
		}
		db.close();
		return drinkPoints;
	}

	public void insertDrinkEntry(DrinkEntry drinkEntry) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put(ALCOHOL_CONCENTRATION, drinkEntry.getConcentration());
		contentValues.put(VOLUME, drinkEntry.getVolume());
		String nameEscaped = StringHelper.avoidInjectionString(drinkEntry.getName());
		contentValues.put(DRINK_NAME, nameEscaped);
		String noteEscaped = StringHelper.avoidInjectionString(drinkEntry.getNote());
		contentValues.put(NOTE, noteEscaped);
		contentValues.put(PRICE, drinkEntry.getPrice());
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		contentValues.put(TIME_DRINKED, dateFormat.format(new Date()));
		db.insert(DRINK_HISTORY_TABLE_NAME, null, contentValues);
		db.close();
	}

	public void insertDrinkEntry(String name, String note, double rating, double latitude, double longitude) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put(ALCOHOL_CONCENTRATION, latitude);
		contentValues.put(VOLUME, longitude);
		String nameEscaped = StringHelper.avoidInjectionString(name);
		contentValues.put(DRINK_NAME, nameEscaped);
		String noteEscaped = StringHelper.avoidInjectionString(note);
		contentValues.put(NOTE, noteEscaped);
		contentValues.put(PRICE, rating);
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		contentValues.put(TIME_DRINKED, dateFormat.format(new Date()));
		db.insert(DRINK_HISTORY_TABLE_NAME, null, contentValues);
		db.close();
	}

	public void updateDrinkEntry(Integer id, String name, String note, double price, double concentration, double volume, Date timeDrinked) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getWritableDatabase();
			ContentValues contentValues = new ContentValues();
			String nameEscaped = StringHelper.avoidInjectionString(name);
			contentValues.put(DRINK_NAME, nameEscaped);
			String noteEscaped = StringHelper.avoidInjectionString(note);
			contentValues.put(NOTE, noteEscaped);
			contentValues.put(PRICE, price);
			contentValues.put(ALCOHOL_CONCENTRATION, concentration);
			contentValues.put(VOLUME, volume);
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			contentValues.put(TIME_DRINKED, dateFormat.format(timeDrinked));
			db.update(DRINK_HISTORY_TABLE_NAME, contentValues, ID + " = " + id.toString(), null);
			db.close();
		}
	}

	public void updateDrinkEntry(Integer id, String name, String note, double price, double concentration, double volume) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getWritableDatabase();
			ContentValues contentValues = new ContentValues();
			String nameEscaped = StringHelper.avoidInjectionString(name);
			contentValues.put(DRINK_NAME, nameEscaped);
			String noteEscaped = StringHelper.avoidInjectionString(note);
			contentValues.put(NOTE, noteEscaped);
			contentValues.put(PRICE, price);
			contentValues.put(ALCOHOL_CONCENTRATION, concentration);
			contentValues.put(VOLUME, volume);
			db.update(DRINK_HISTORY_TABLE_NAME, contentValues, ID + " = " + id.toString(), null);
			db.close();
		}
	}

	public void updateDrinkEntry(Integer id, String name, String note, double rating, Date timeDrinked) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getWritableDatabase();
			ContentValues contentValues = new ContentValues();
			String nameEscaped = StringHelper.avoidInjectionString(name);
			contentValues.put(DRINK_NAME, nameEscaped);
			String noteEscaped = StringHelper.avoidInjectionString(note);
			contentValues.put(NOTE, noteEscaped);
			contentValues.put(PRICE, rating);
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			contentValues.put(TIME_DRINKED, dateFormat.format(timeDrinked));
			db.update(DRINK_HISTORY_TABLE_NAME, contentValues, ID + " = " + id.toString(), null);
			db.close();
		}
	}

	public void updateDrinkEntry(Integer id, String name, String note, double rating) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getWritableDatabase();
			ContentValues contentValues = new ContentValues();
			String nameEscaped = StringHelper.avoidInjectionString(name);
			contentValues.put(DRINK_NAME, nameEscaped);
			String noteEscaped = StringHelper.avoidInjectionString(note);
			contentValues.put(NOTE, noteEscaped);
			contentValues.put(PRICE, rating);
			db.update(DRINK_HISTORY_TABLE_NAME, contentValues, ID + " = " + id.toString(), null);
			db.close();
		}
	}

	public void updateDrinkEntry(Integer id, DrinkEntry drinkEntry) {
		updateDrinkEntry(id, drinkEntry.getName(), drinkEntry.getNote(), drinkEntry.getPrice(), drinkEntry.getConcentration(), drinkEntry.getVolume(), drinkEntry.getTimeDrinked());
	}

	public void removeDrinkEntry(Integer id) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getWritableDatabase();

			db.delete(DRINK_HISTORY_TABLE_NAME, ID + " = " + id.toString(), null);
			db.close();
		}
	}

	public void removeDrinkEntry(DrinkEntry drinkPoint) {
		if (drinkPoint != null && drinkPoint.getId() != null && !"".equals(drinkPoint.getId())) {
			SQLiteDatabase db = getWritableDatabase();

			db.delete(DRINK_HISTORY_TABLE_NAME, ID + " = " + drinkPoint.getId().toString(), null);
			db.close();
		}
	}

	// public void removeDrinkEntry(Integer id, DrinkEntry drinkEntry) {
	// if (id != null && !"".equals(id.toString())) {
	// SQLiteDatabase db = getWritableDatabase();
	// ContentValues contentValues = new ContentValues();
	// contentValues.put(DRINK_NAME, drinkEntry.getName());
	// contentValues.put(NOTE, drinkEntry.getNote());
	// contentValues.put(PRICE, drinkEntry.getPrice());
	// db.update(DRINK_HISTORY_TABLE_NAME, contentValues, ID + " = " +
	// id.toString(), null);
	// db.close();
	// }
	// }

	public DrinkEntry getDrinkEntryByID(Integer id) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getReadableDatabase();
			Cursor cursor = db.query(DRINK_HISTORY_TABLE_NAME, new String[] { TIME_DRINKED, DRINK_NAME, NOTE, PRICE, ALCOHOL_CONCENTRATION, VOLUME }, ID + " = " + id.toString(), null, null, null,
					TIME_DRINKED + " DESC");
			DrinkEntry drinkEntry = null;
			if (cursor.getCount() > 0 && cursor.getCount() == 1 && cursor.moveToNext()) {
				String place = cursor.getString(cursor.getColumnIndex(DRINK_NAME));
				String note = cursor.getString(cursor.getColumnIndex(NOTE));
				double rating = cursor.getDouble(cursor.getColumnIndex(PRICE));
				Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME_DRINKED)));
				Date time = new Date(timeOfLocation.getTime());
				double latitude = cursor.getDouble(cursor.getColumnIndex(ALCOHOL_CONCENTRATION));
				double longitude = cursor.getDouble(cursor.getColumnIndex(VOLUME));
				drinkEntry = new DrinkEntry(id, latitude, longitude, place, note, rating, time);
			}
			db.close();
			return drinkEntry;
		}
		return null;
	}

	public ArrayList<DrinkEntry> getDrinkEntryBefore(Date beforeDateTime) {
		ArrayList<DrinkEntry> drinkEntries = new ArrayList<DrinkEntry>();
		if (beforeDateTime != null) {
			// Timestamp beforeDateTimeStamp = new
			// Timestamp(beforeDateTime.getTime());
			SQLiteDatabase db = getReadableDatabase();
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Cursor cursor = db.query(DRINK_HISTORY_TABLE_NAME, new String[] { ID, TIME_DRINKED, DRINK_NAME, NOTE, PRICE, ALCOHOL_CONCENTRATION, VOLUME }, TIME_DRINKED + " < " + "Datetime('"
					+ dateFormat.format(beforeDateTime) + "')", null, null, null, TIME_DRINKED + " DESC");
			while (cursor.getCount() > 0 && cursor.moveToNext()) {
				// DrinkEntry drinkEntry = null;
				// if (cursor.getCount() > 0 && cursor.getCount() == 1 &&
				// cursor.moveToNext()) {
				int id = cursor.getInt(cursor.getColumnIndex(ID));
				String place = cursor.getString(cursor.getColumnIndex(DRINK_NAME));
				String note = cursor.getString(cursor.getColumnIndex(NOTE));
				double rating = cursor.getDouble(cursor.getColumnIndex(PRICE));
				Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME_DRINKED)));
				Date time = new Date(timeOfLocation.getTime());
				double latitude = cursor.getDouble(cursor.getColumnIndex(ALCOHOL_CONCENTRATION));
				double longitude = cursor.getDouble(cursor.getColumnIndex(VOLUME));
				// drinkEntry = new DrinkEntry(id, latitude, longitude, place,
				// note, rating, time);
				drinkEntries.add(new DrinkEntry(id, latitude, longitude, place, note, rating, time));
				// }
			}
			db.close();
			return drinkEntries;
		}
		return null;
	}

	public ArrayList<DrinkEntry> getDrinkEntryAfter(Date afterDateTime) {
		ArrayList<DrinkEntry> drinkEntries = new ArrayList<DrinkEntry>();

		if (afterDateTime != null) {
			// Timestamp afterDateTimeStamp = new
			// Timestamp(afterDateTime.getTime());
			SQLiteDatabase db = getReadableDatabase();
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Cursor cursor = db.query(DRINK_HISTORY_TABLE_NAME, new String[] { ID, TIME_DRINKED, DRINK_NAME, NOTE, PRICE, ALCOHOL_CONCENTRATION, VOLUME }, TIME_DRINKED + " > " + "Datetime('"
					+ dateFormat.format(afterDateTime) + "')", null, null, null, TIME_DRINKED + " DESC");
			while (cursor.getCount() > 0 && cursor.moveToNext()) {
				// DrinkEntry drinkEntry = null;
				// if (cursor.getCount() > 0 /*&& cursor.getCount() == 1 */&&
				// cursor.moveToNext()) {
				int id = cursor.getInt(cursor.getColumnIndex(ID));
				String place = cursor.getString(cursor.getColumnIndex(DRINK_NAME));
				String note = cursor.getString(cursor.getColumnIndex(NOTE));
				double rating = cursor.getDouble(cursor.getColumnIndex(PRICE));
				Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME_DRINKED)));
				Date time = new Date(timeOfLocation.getTime());
				double latitude = cursor.getDouble(cursor.getColumnIndex(ALCOHOL_CONCENTRATION));
				double longitude = cursor.getDouble(cursor.getColumnIndex(VOLUME));
				// drinkEntry = new DrinkEntry(id, latitude, longitude, place,
				// note, rating, time);
				drinkEntries.add(new DrinkEntry(id, latitude, longitude, place, note, rating, time));
				// }
			}
			db.close();
			return drinkEntries;
		}
		return null;
	}

	public ArrayList<DrinkEntry> getDrinkEntryAfterForName(Date afterDateTime, String name) {
		if (afterDateTime != null) {
			// Timestamp afterDateTimeStamp = new
			// Timestamp(afterDateTime.getTime());
			ArrayList<DrinkEntry> drinkEntries = new ArrayList<DrinkEntry>();
			SQLiteDatabase db = getReadableDatabase();
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Cursor cursor = db.query(DRINK_HISTORY_TABLE_NAME, new String[] { ID, TIME_DRINKED, DRINK_NAME, NOTE, PRICE, ALCOHOL_CONCENTRATION, VOLUME }, TIME_DRINKED + " > " + "Datetime('"
					+ dateFormat.format(afterDateTime) + "')" + " AND " + DRINK_NAME + " IS " + "'" + StringHelper.avoidInjectionString(name) + "'", null, null, null, TIME_DRINKED + " DESC");
			while (cursor.getCount() > 0 && cursor.moveToNext()) {
				// DrinkEntry drinkEntry = null;
				// if (cursor.getCount() > 0 /*&& cursor.getCount() == 1 */&&
				// cursor.moveToNext()) {
				int id = cursor.getInt(cursor.getColumnIndex(ID));
				String place = cursor.getString(cursor.getColumnIndex(DRINK_NAME));
				String note = cursor.getString(cursor.getColumnIndex(NOTE));
				double rating = cursor.getDouble(cursor.getColumnIndex(PRICE));
				Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME_DRINKED)));
				Date time = new Date(timeOfLocation.getTime());
				double latitude = cursor.getDouble(cursor.getColumnIndex(ALCOHOL_CONCENTRATION));
				double longitude = cursor.getDouble(cursor.getColumnIndex(VOLUME));
				// drinkEntry = new DrinkEntry(id, latitude, longitude, place,
				// note, rating, time);
				drinkEntries.add(new DrinkEntry(id, latitude, longitude, place, note, rating, time));
				// }
			}
			db.close();
			return drinkEntries;
		}
		return null;
	}

	public Integer countDrinkEntryAfterForName(Date afterDateTime, String name) {
		if (afterDateTime != null && name != null && !"".equals(name.trim())) {
			// Timestamp afterDateTimeStamp = new
			// Timestamp(afterDateTime.getTime());
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			// ArrayList<DrinkEntry> drinkEntries = new ArrayList<DrinkEntry>();
			SQLiteDatabase db = getReadableDatabase();
			Cursor cursor = db.query(DRINK_HISTORY_TABLE_NAME, new String[] { TIME_DRINKED, DRINK_NAME, NOTE, PRICE, ALCOHOL_CONCENTRATION, VOLUME },
					TIME_DRINKED + " > " + "Datetime('" + dateFormat.format(afterDateTime) + "')" + " AND " + DRINK_NAME + " IS " + "'" + StringHelper.avoidInjectionString(name) + "'", null, null,
					null, TIME_DRINKED + " DESC");
			Integer entryCount = cursor.getCount();
			db.close();
			return entryCount;
		}
		return 0;
	}
}
